package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.region.RegionBean;
import com.yzyz.dao.RegionDao;
import com.yzyz.utils.mapper.RegionRowMapper;

/**
 * 
 * @author David Sun
 * @Date   2010-3-28
 *
 */
public class RegionDaoImpl implements RegionDao {
	private JdbcTemplate jdbcTemplate;
	@SuppressWarnings("unchecked")
	@Override
	public RegionBean get(final String name) {
		String sql = "select * from region where name=?";
		List<RegionBean> regions = this.jdbcTemplate.query(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, name);
			}
		}, new RegionRowMapper());
		if(regions == null || regions.size() != 1) {
			return null;
		} 
		return regions.get(0);
	}

	@Override
	public int delete(int id) {
		String sql = "delete from region where id=" + id;
		return this.jdbcTemplate.update(sql);

	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(final int id) {
		String sql = "select * from region where id=?";
		List<RegionBean> regions = this.jdbcTemplate.query(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, id);
			}
		}, new RegionRowMapper());
		if(regions == null || regions.size() != 1) {
			return null;
		} 
		return regions.get(0);
	}

	@Override
	public int save(Object object) {
		final RegionBean region = (RegionBean)object;
		final String sql = "insert into region (name,date,available,level, description) values (?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection conn) throws SQLException {
				java.sql.PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, region.getName());
				ps.setBoolean(3, region.isAvailable());
				ps.setTimestamp(2, new java.sql.Timestamp(region.getDate().getTime()));
				ps.setInt(4, region.getLevel());
				ps.setString(5, region.getDescription());
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}

	@Override
	public void update(final Object object) {
		final RegionBean region = (RegionBean)object;
		String sql = "update region set name=?, description=?, level=? where id=?";
		this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, region.getName());
				ps.setString(2, region.getDescription());
				ps.setInt(3, region.getLevel());
				ps.setInt(4, region.getId());
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RegionBean> getAll() {
		String sql = "select * from region";
		List<RegionBean> regions = this.jdbcTemplate.query(sql, new RegionRowMapper());
		return regions;
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
		
	}

	@Override
	public int updateAvailable(final int id, final boolean available) {
		String sql = "update region set available=? where id=?";
		return this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setBoolean(1, available);
				ps.setInt(2, id);
			}
		});
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RegionBean> getAllWithoutDisabled() {
		String sql = "select * from region where available=1 order by level desc";
		List<RegionBean> regions = this.jdbcTemplate.query(sql, new RegionRowMapper());
		return regions;
	}

	@Override
	public int getCount(int ownerId) {
		String sql = "select count(*) from region where available=1";
		return this.jdbcTemplate.queryForInt(sql);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RegionBean> getRegions(int start, int limit) {
		String sql = "select * from region limit " + start + ", " + limit;
		return this.jdbcTemplate.query(sql, new RegionRowMapper());
	}

}
